package com.zx.zhilian;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.annotation.JSONField;
import com.zx.common.C3P0Utils;
import com.zx.ziroom.ZiRoomCity;

public class ZhiLianStatisticsUtil {
	
	public static void main(String[] args) {
		List<ZhiLianStatistics> statistics = statistics();
		//排序
		Collections.sort(statistics, new Comparator<ZhiLianStatistics>() {
			@Override
			public int compare(ZhiLianStatistics o1, ZhiLianStatistics o2) {
				if(o1.getCity().compareTo(o2.getCity()) == 0) {//1.城市
					if(o1.getJob().compareTo(o2.getJob()) == 0) {
						return o1.getTime().compareTo(o2.getTime());
					} else {
						return o1.getJob().compareTo(o2.getJob());
					}
				} else {
					return o1.getCity().compareTo(o2.getCity());
				}
			}
		});
		//生成json
		for (ZhiLianStatistics zhiLianStatistics : statistics) {
			System.out.println(JSONObject.toJSON(zhiLianStatistics).toString() + ",");
		}
	}
	
	public static List<ZhiLianStatistics> statistics() {
		List<ZhiLianStatistics> result = new ArrayList<>();
		ZiRoomCity[] values = ZiRoomCity.values();
		for (ZiRoomCity ziRoomCity : values) {
			String city = ziRoomCity.getName();
			List<ZhiLianStatistics> statistics = statistics(city);
			result.addAll(statistics);
		}
		return result;
	}
	
	/**
	 * @Title: 统计一个城市的数据
	 * @param city
	 * @return
	 * @date: 2017年12月7日下午9:38:45
	 */
	private static List<ZhiLianStatistics> statistics(String city) {
		Connection connection = C3P0Utils.getConnection();
		QueryRunner query = new QueryRunner();
		String sql = "select '"+city+"' city ,ty.k job, z.工作经验 time, z.职位月薪 priceStr, count(*) count " + 
				"from (select 关键词 k from zhilian group by 关键词 ) ty left join zhilian z on ty.k = z.关键词 " + 
				"where z.工作地点 like '%"+city+"%' and z.工作性质='全职' and 职位月薪 <> '面议 ' " +
				"group by ty.k, z.工作经验, z.职位月薪";
		List<ZhiLianStatistics> list = null;
		try {
			list = query.query(connection, sql, new BeanListHandler<ZhiLianStatistics>(ZhiLianStatistics.class));
			
			if(list == null || list.isEmpty())
				return null;
			
			List<ZhiLianStatistics> result = new ArrayList<>();
			
			// key = job, value=这个职位的所有数据
			HashMap<String, List<ZhiLianStatistics>> map = new HashMap<>();
			// 1. 按职位分类
			list.forEach(e -> {
				String job = e.getJob();
				if(!map.containsKey(job)){
					List<ZhiLianStatistics> li = new ArrayList<>();
					map.put(job, li);
				} 
				map.get(job).add(e);
			});
			
			Set<Entry<String,List<ZhiLianStatistics>>> entrySet = map.entrySet();
			entrySet.forEach(e -> {
				String job = e.getKey();
				//获得某个职位的数据
				List<ZhiLianStatistics> infos = e.getValue();
				// key=time,value=这个工作经验的人的数据
				HashMap<String, List<ZhiLianStatistics>> timeMap = new HashMap<>();
				// 2. 获得某个职位下，某个工作经验的集合
				infos.forEach(info -> {
					String time = info.getTime();
					if(!timeMap.containsKey(time)){
						List<ZhiLianStatistics> li = new ArrayList<>();
						timeMap.put(time, li);
					} 
					timeMap.get(time).add(info);
				});
				
				// 3. 计算某个职位下，某个工作经验的平均工资,就是一条数据
				Set<Entry<String,List<ZhiLianStatistics>>> es = timeMap.entrySet();
				es.forEach(timeInfos -> {
					String time = timeInfos.getKey();
					List<ZhiLianStatistics> value = timeInfos.getValue();
					
					ZhiLianStatistics timeStatistics = new ZhiLianStatistics(city, job, time, "", 0L, 0);
					
					value.forEach(t -> {
						try {
							String replace = t.getPriceStr().substring(0, t.getPriceStr().indexOf("元/月"));
							if(replace.contains("-")) {//只计算有范围的
								String[] split = replace.split("-");
								Long parseInt1 = Long.parseLong(split[0]);//最低工资
								Long parseInt2 = Long.parseLong(split[1]);//最高工资
								timeStatistics.setPrice(timeStatistics.getPrice() +  (parseInt1 + parseInt2 ) * t.getCount() );
								timeStatistics.setCount(timeStatistics.getCount() + t.getCount() * 2);
							}
						} catch (Exception e1) {
							e1.printStackTrace();
						}
					});
					if(timeStatistics.getCount() != 0)
						timeStatistics.setPrice(timeStatistics.getPrice() / timeStatistics.getCount());
					
					result.add(timeStatistics);
				});
			});
			list = result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static class ZhiLianStatistics {
		@JSONField(name = "city")
		private String city;
		
		@JSONField(name = "job")
		private String job;
		
		@JSONField(name = "time")
		private String time;
		
		@JSONField(serialize=false)
		private String priceStr;
		
		@JSONField(name = "price")
		private Long price;
		
		@JSONField(serialize=false)
		private Integer count;
		
		public ZhiLianStatistics() {		}

		public ZhiLianStatistics(String city, String job, String time, String priceStr, Long price, Integer count) {
			this.city = city;
			this.job = job;
			this.time = time;
			this.priceStr = priceStr;
			this.price = price;
			this.count = count;
		}
		
		public String getCity() {
			return city;
		}

		public void setCity(String city) {
			this.city = city;
		}

		public String getTime() {
			return time;
		}

		public void setTime(String time) {
			this.time = time;
		}

		public String getPriceStr() {
			return priceStr;
		}

		public void setPriceStr(String priceStr) {
			this.priceStr = priceStr;
		}

		public Long getPrice() {
			return price;
		}

		public void setPrice(Long price) {
			this.price = price;
		}

		public Integer getCount() {
			return count;
		}

		public void setCount(Integer count) {
			this.count = count;
		}

		public String getJob() {
			return job;
		}

		public void setJob(String job) {
			this.job = job;
		}

		@Override
		public String toString() {
			return "ZhiLianStatistics [city=" + city + ", job=" + job + ", time=" + time + ", price=" + price + "]";
		}
	}
	
	
}
